package com.ruoyi.agriculture.knowledge.controller;

import com.ruoyi.common.annotation.Anonymous;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import org.springframework.web.bind.annotation.*;

import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * JDK 8兼容版知识图谱构建控制器
 * @author 宋瑞智
 */
@Anonymous
@RestController
@RequestMapping("/knowledge/graph")
public class SimpleGraphController extends BaseController {

    // 数据库连接配置
    private static final String DB_URL = "jdbc:mysql://localhost:3306/ry-vue?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "123456"; // 修改为你的数据库密码

    /**
     * 清空知识图谱数据
     */
    @GetMapping("/clear")
    public AjaxResult clearKnowledgeGraph() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            clearExistingData(conn);
            return success("知识图谱数据清空成功");
        } catch (Exception e) {
            e.printStackTrace();
            return error("清空失败: " + e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 重新构建知识图谱数据
     */
    @GetMapping("/rebuild")
    public AjaxResult rebuildKnowledgeGraph() {
        Connection conn = null;
        try {
            System.out.println("开始重新构建知识图谱...");

            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);

            // 1. 清空现有知识图谱数据
            clearExistingData(conn);

            // 2. 重新生成作物节点
            generateCropNodes(conn);

            // 3. 基于真实品种数据生成品种节点
            generateVarietyNodesFromDB(conn);

            // 4. 从品种特征中提取特征节点
            generateFeatureNodesFromDB(conn);

            // 5. 生成关系数据
            generateRelationsFromDB(conn);

            System.out.println("知识图谱重建完成！");
            return success("知识图谱重建成功");

        } catch (Exception e) {
            e.printStackTrace();
            return error("知识图谱重建失败: " + e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 获取知识图谱数据
     */
    @GetMapping("/data")
    public AjaxResult getGraphData(@RequestParam(defaultValue = "crop_1") String centerNode) {
        Connection conn = null;
        try {
            Map<String, Object> result = new HashMap<String, Object>();

            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);

            // 获取节点数据
            List<Map<String, Object>> nodes = getGraphNodes(conn, centerNode);

            // 获取关系数据
            List<Map<String, Object>> links = getGraphLinks(conn, centerNode);

            result.put("nodes", nodes);
            result.put("links", links);
            result.put("centerNode", centerNode);

            return success(result);

        } catch (Exception e) {
            e.printStackTrace();
            return error("获取知识图谱数据失败: " + e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 获取搜索建议
     */
    @GetMapping("/suggestions")
    public AjaxResult getSearchSuggestions(@RequestParam String keyword) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);

            List<Map<String, Object>> suggestions = new ArrayList<>();

            // 搜索作物
            if ("小麦".contains(keyword) || "xiaomai".contains(keyword.toLowerCase())) {
                Map<String, Object> crop = new HashMap<>();
                crop.put("id", "crop_1");
                crop.put("name", "小麦");
                crop.put("type", "crop");
                crop.put("description", "作物分类");
                suggestions.add(crop);
            }

            if ("玉米".contains(keyword) || "yumi".contains(keyword.toLowerCase())) {
                Map<String, Object> crop = new HashMap<>();
                crop.put("id", "crop_2");
                crop.put("name", "玉米");
                crop.put("type", "crop");
                crop.put("description", "作物分类");
                suggestions.add(crop);
            }

            // 搜索品种
            String sql = "SELECT cv.id, cv.variety_name, cv.category_id, cc.name as crop_name " +
                    "FROM zs_crop_variety cv " +
                    "LEFT JOIN zs_crop_category cc ON cv.category_id = cc.id " +
                    "WHERE cv.variety_name LIKE ? AND cv.status = 1 " +
                    "ORDER BY cv.variety_name " +
                    "LIMIT 10";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Map<String, Object> variety = new HashMap<>();
                variety.put("id", "variety_" + rs.getLong("id"));
                variety.put("name", rs.getString("variety_name"));
                variety.put("type", "variety");
                variety.put("description", rs.getString("crop_name") + "品种");
                variety.put("categoryId", rs.getLong("category_id"));
                suggestions.add(variety);
            }

            return success(suggestions);

        } catch (Exception e) {
            e.printStackTrace();
            return error("获取搜索建议失败: " + e.getMessage());
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 搜索节点
     */
    @GetMapping("/search")
    public AjaxResult searchNodes(@RequestParam String keyword, @RequestParam(required = false) String nodeId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);

            String centerNode = null;
            String searchType = "crop";
            String actualNodeId = null;

            // 如果提供了具体的nodeId，直接使用
            if (nodeId != null && !nodeId.trim().isEmpty()) {
                centerNode = nodeId;
                actualNodeId = nodeId;

                if (nodeId.startsWith("crop_")) {
                    searchType = "crop";
                } else if (nodeId.startsWith("variety_")) {
                    searchType = "variety";
                }
            } else {
                // 原有的搜索逻辑
                if (keyword.contains("小麦")) {
                    centerNode = "crop_1";
                } else if (keyword.contains("玉米")) {
                    centerNode = "crop_2";
                } else {
                    // 搜索品种名称
                    String sql = "SELECT cv.id, cv.variety_name, cv.category_id " +
                            "FROM zs_crop_variety cv " +
                            "WHERE cv.variety_name LIKE ? AND cv.status = 1 " +
                            "LIMIT 1";

                    pstmt = conn.prepareStatement(sql);
                    pstmt.setString(1, "%" + keyword + "%");
                    rs = pstmt.executeQuery();

                    if (rs.next()) {
                        Long varietyId = rs.getLong("id");
                        String varietyName = rs.getString("variety_name");

                        centerNode = "variety_" + varietyId;  // 以品种为中心
                        actualNodeId = centerNode;
                        searchType = "variety";

                        System.out.println("找到品种: " + varietyName + ", 节点ID: " + centerNode);
                    }
                }
            }

            if (centerNode != null) {
                Map<String, Object> result = new HashMap<String, Object>();
                result.put("centerNode", centerNode);
                result.put("keyword", keyword);
                result.put("searchType", searchType);
                result.put("actualNodeId", actualNodeId);
                return success(result);
            } else {
                return error("未找到相关节点，请搜索作物名称（如：小麦、玉米）或品种名称");
            }

        } catch (Exception e) {
            e.printStackTrace();
            return error("搜索失败: " + e.getMessage());
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取节点详情
     */
    @GetMapping("/node/{nodeId}")
    public AjaxResult getNodeDetail(@PathVariable String nodeId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);

            String sql = "SELECT node_id, node_name, node_type, description, " +
                    "CASE WHEN node_type = 'variety' THEN SUBSTRING(node_id, 9) ELSE NULL END as variety_id " +
                    "FROM zs_knowledge_nodes WHERE node_id = ?";

            Map<String, Object> result = new HashMap<String, Object>();

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, nodeId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                result.put("nodeId", rs.getString("node_id"));
                result.put("nodeName", rs.getString("node_name"));
                result.put("nodeType", rs.getString("node_type"));
                result.put("description", rs.getString("description"));
                result.put("varietyId", rs.getString("variety_id"));
            }

            return success(result);

        } catch (Exception e) {
            return error("获取节点详情失败: " + e.getMessage());
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // =================================
    // 私有方法实现
    // =================================

    private void clearExistingData(Connection conn) throws SQLException {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM zs_knowledge_relations");
            stmt.executeUpdate("DELETE FROM zs_knowledge_nodes");
            System.out.println("✓ 清空旧数据完成");
        } finally {
            if (stmt != null) stmt.close();
        }
    }

    private void generateCropNodes(Connection conn) throws SQLException {
        Statement stmt = null;
        try {
            String sql = "INSERT INTO zs_knowledge_nodes (node_id, node_name, node_type, description, create_time) " +
                    "SELECT CONCAT('crop_', id), name, 'crop', description, NOW() FROM zs_crop_category";
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
            System.out.println("✓ 作物节点生成完成");
        } finally {
            if (stmt != null) stmt.close();
        }
    }

    private void generateVarietyNodesFromDB(Connection conn) throws SQLException {
        Statement stmt = null;
        try {
            String sql = "INSERT INTO zs_knowledge_nodes (node_id, node_name, node_type, parent_id, description, create_time) " +
                    "SELECT CONCAT('variety_', cv.id), cv.variety_name, 'variety', CONCAT('crop_', cv.category_id), " +
                    "CONCAT(COALESCE(cv.breeding_unit, '未知'), '选育，', COALESCE(cv.approval_year, ''), '年审定'), NOW() " +
                    "FROM zs_crop_variety cv WHERE cv.status = 1";
            stmt = conn.createStatement();
            int count = stmt.executeUpdate(sql);
            System.out.println("✓ 品种节点生成完成，共生成 " + count + " 个品种节点");
        } finally {
            if (stmt != null) stmt.close();
        }
    }

    private void generateFeatureNodesFromDB(Connection conn) throws SQLException {
        PreparedStatement selectStmt = null;
        ResultSet rs = null;

        try {
            // 获取所有品种的特征描述
            String selectSql = "SELECT id, variety_name, characteristics FROM zs_crop_variety WHERE status = 1 AND characteristics IS NOT NULL";

            // 先查询当前最大的特征ID，避免重复
            int featureId = getMaxFeatureId(conn) + 1;
            int totalFeatures = 0;

            selectStmt = conn.prepareStatement(selectSql);
            rs = selectStmt.executeQuery();

            while (rs.next()) {
                Long varietyId = rs.getLong("id");
                String varietyName = rs.getString("variety_name");
                String characteristics = rs.getString("characteristics");

                if (characteristics != null && !characteristics.trim().isEmpty()) {
                    // 提取特征
                    List<String> features = extractFeaturesFromText(characteristics);

                    for (String feature : features) {
                        String nodeId = "feature_" + featureId++;
                        // 检查节点ID是否已存在，如果存在则跳过
                        if (!nodeExists(conn, nodeId)) {
                            insertFeatureNode(conn, nodeId, feature, varietyId, varietyName);
                            totalFeatures++;
                        }
                    }
                }
            }

            System.out.println("✓ 特征节点提取完成，共提取 " + totalFeatures + " 个特征");

        } finally {
            if (rs != null) rs.close();
            if (selectStmt != null) selectStmt.close();
        }
    }

    /**
     * 获取当前最大的特征ID编号
     */
    private int getMaxFeatureId(Connection conn) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            String sql = "SELECT COALESCE(MAX(CAST(SUBSTRING(node_id, 9) AS UNSIGNED)), 0) as max_id " +
                    "FROM zs_knowledge_nodes WHERE node_id LIKE 'feature_%'";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("max_id");
            }
            return 0;
        } finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
        }
    }

    /**
     * 检查节点是否已存在
     */
    private boolean nodeExists(Connection conn, String nodeId) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            String sql = "SELECT COUNT(*) FROM zs_knowledge_nodes WHERE node_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, nodeId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        } finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
        }
    }

    private List<String> extractFeaturesFromText(String text) {
        List<String> features = new ArrayList<String>();

        // 定义特征提取模式
        Pattern[] patterns = {
                Pattern.compile("(春性|冬性|半冬性)[^，。；]*品种"),
                Pattern.compile("(早熟|中熟|晚熟)[^，。；]*"),
                Pattern.compile("(高抗|中抗|抗|感|高感)([^，。；]*)(病|虫害)"),
                Pattern.compile("抗寒性(较好|好|强|较强|一般)"),
                Pattern.compile("抗倒性(较好|好|强|较强|一般)"),
                Pattern.compile("株高[\\d\\.]+.*?厘米"),
                Pattern.compile("(纺锤型|长方形|椭圆形)穗"),
                Pattern.compile("(长芒|短芒|无芒)"),
                Pattern.compile("(白壳|红壳)"),
                Pattern.compile("(白粒|红粒)"),
                Pattern.compile("籽粒(硬质|半硬质|软质)"),
                Pattern.compile("粗蛋白.*?[\\d\\.]+%"),
                Pattern.compile("千粒重[\\d\\.]+.*?克")
        };

        for (Pattern pattern : patterns) {
            Matcher matcher = pattern.matcher(text);
            while (matcher.find()) {
                String feature = matcher.group().trim();
                if (feature.length() > 2 && feature.length() < 30) {
                    features.add(feature);
                }
            }
        }

        // 如果没有提取到特征，添加一个默认特征
        if (features.isEmpty()) {
            features.add("待分析特征");
        }

        return features;
    }

    private void insertFeatureNode(Connection conn, String nodeId, String featureName, Long varietyId, String varietyName) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            String sql = "INSERT INTO zs_knowledge_nodes (node_id, node_name, node_type, parent_id, description, create_time) " +
                    "VALUES (?, ?, 'feature', ?, ?, NOW())";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, nodeId);
            pstmt.setString(2, featureName);
            pstmt.setString(3, "variety_" + varietyId);
            pstmt.setString(4, varietyName + "的特征");
            pstmt.executeUpdate();
        } finally {
            if (pstmt != null) pstmt.close();
        }
    }

    private void generateRelationsFromDB(Connection conn) throws SQLException {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();

            // 作物包含品种的关系
            String sql1 = "INSERT INTO zs_knowledge_relations (source_node_id, target_node_id, relation_type, relation_label, create_time) " +
                    "SELECT CONCAT('crop_', cv.category_id), CONCAT('variety_', cv.id), '包含', '品种', NOW() " +
                    "FROM zs_crop_variety cv WHERE cv.status = 1";
            int count1 = stmt.executeUpdate(sql1);

            // 品种具有特征的关系
            String sql2 = "INSERT INTO zs_knowledge_relations (source_node_id, target_node_id, relation_type, relation_label, create_time) " +
                    "SELECT kn1.node_id, kn2.node_id, '具有', '特征', NOW() " +
                    "FROM zs_knowledge_nodes kn1 " +
                    "JOIN zs_knowledge_nodes kn2 ON kn1.node_id = kn2.parent_id " +
                    "WHERE kn1.node_type = 'variety' AND kn2.node_type = 'feature'";
            int count2 = stmt.executeUpdate(sql2);

            System.out.println("✓ 关系数据生成完成，作物-品种关系: " + count1 + " 个，品种-特征关系: " + count2 + " 个");
        } finally {
            if (stmt != null) stmt.close();
        }
    }

    private List<Map<String, Object>> getGraphNodes(Connection conn, String centerNode) throws SQLException {
        List<Map<String, Object>> nodes = new ArrayList<Map<String, Object>>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            if (centerNode.startsWith("crop_")) {
                // 作物为中心：分步查询避免MySQL LIMIT IN子查询错误

                // 第一步：获取中心作物节点
                String cropSql = "SELECT node_id, node_name, node_type, description, " +
                        "3 as level FROM zs_knowledge_nodes WHERE node_id = ?";
                pstmt = conn.prepareStatement(cropSql);
                pstmt.setString(1, centerNode);
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    Map<String, Object> node = new HashMap<String, Object>();
                    node.put("id", rs.getString("node_id"));
                    node.put("name", rs.getString("node_name"));
                    node.put("type", rs.getString("node_type"));
                    node.put("description", rs.getString("description"));
                    node.put("level", rs.getInt("level"));
                    nodes.add(node);
                }
                rs.close();
                pstmt.close();

                // 第二步：获取限量品种节点（最多8个）
                String varietySql = "SELECT node_id, node_name, node_type, description, " +
                        "2 as level FROM zs_knowledge_nodes " +
                        "WHERE node_type = 'variety' AND parent_id = ? " +
                        "ORDER BY node_name LIMIT 8";
                pstmt = conn.prepareStatement(varietySql);
                pstmt.setString(1, centerNode);
                rs = pstmt.executeQuery();

                List<String> varietyIds = new ArrayList<>();
                while (rs.next()) {
                    Map<String, Object> node = new HashMap<String, Object>();
                    node.put("id", rs.getString("node_id"));
                    node.put("name", rs.getString("node_name"));
                    node.put("type", rs.getString("node_type"));
                    node.put("description", rs.getString("description"));
                    node.put("level", rs.getInt("level"));
                    nodes.add(node);

                    varietyIds.add(rs.getString("node_id"));
                }
                rs.close();
                pstmt.close();

                // 第三步：获取这些品种的特征节点
                if (!varietyIds.isEmpty()) {
                    StringBuilder featureSql = new StringBuilder();
                    featureSql.append("SELECT node_id, node_name, node_type, description, ");
                    featureSql.append("1 as level FROM zs_knowledge_nodes ");
                    featureSql.append("WHERE node_type = 'feature' AND parent_id IN (");

                    for (int i = 0; i < varietyIds.size(); i++) {
                        if (i > 0) featureSql.append(",");
                        featureSql.append("?");
                    }
                    featureSql.append(")");

                    pstmt = conn.prepareStatement(featureSql.toString());
                    for (int i = 0; i < varietyIds.size(); i++) {
                        pstmt.setString(i + 1, varietyIds.get(i));
                    }
                    rs = pstmt.executeQuery();

                    while (rs.next()) {
                        Map<String, Object> node = new HashMap<String, Object>();
                        node.put("id", rs.getString("node_id"));
                        node.put("name", rs.getString("node_name"));
                        node.put("type", rs.getString("node_type"));
                        node.put("description", rs.getString("description"));
                        node.put("level", rs.getInt("level"));
                        nodes.add(node);
                    }
                }

            } else if (centerNode.startsWith("variety_")) {
                // 品种为中心：分步查询

                // 第一步：获取中心品种节点和其父作物
                String varietySql = "SELECT kn.node_id, kn.node_name, kn.node_type, kn.description, " +
                        "CASE WHEN kn.node_type = 'crop' THEN 3 WHEN kn.node_type = 'variety' THEN 2 ELSE 1 END as level, " +
                        "kn.parent_id " +
                        "FROM zs_knowledge_nodes kn " +
                        "WHERE kn.node_id = ? " +
                        "OR kn.node_id = (SELECT parent_id FROM zs_knowledge_nodes WHERE node_id = ?)";

                pstmt = conn.prepareStatement(varietySql);
                pstmt.setString(1, centerNode);
                pstmt.setString(2, centerNode);
                rs = pstmt.executeQuery();

                String parentCropId = null;
                while (rs.next()) {
                    Map<String, Object> node = new HashMap<String, Object>();
                    node.put("id", rs.getString("node_id"));
                    node.put("name", rs.getString("node_name"));
                    node.put("type", rs.getString("node_type"));
                    node.put("description", rs.getString("description"));
                    node.put("level", rs.getInt("level"));
                    nodes.add(node);

                    if ("variety".equals(rs.getString("node_type"))) {
                        parentCropId = rs.getString("parent_id");
                    }
                }
                rs.close();
                pstmt.close();

                // 第二步：获取同类其他品种（最多6个，排除中心品种）
                if (parentCropId != null) {
                    String siblingsSql = "SELECT node_id, node_name, node_type, description, " +
                            "2 as level FROM zs_knowledge_nodes " +
                            "WHERE node_type = 'variety' AND parent_id = ? AND node_id != ? " +
                            "ORDER BY node_name LIMIT 6";
                    pstmt = conn.prepareStatement(siblingsSql);
                    pstmt.setString(1, parentCropId);
                    pstmt.setString(2, centerNode);
                    rs = pstmt.executeQuery();

                    while (rs.next()) {
                        Map<String, Object> node = new HashMap<String, Object>();
                        node.put("id", rs.getString("node_id"));
                        node.put("name", rs.getString("node_name"));
                        node.put("type", rs.getString("node_type"));
                        node.put("description", rs.getString("description"));
                        node.put("level", rs.getInt("level"));
                        nodes.add(node);
                    }
                    rs.close();
                    pstmt.close();
                }

                // 第三步：获取中心品种的特征节点
                String featureSql = "SELECT node_id, node_name, node_type, description, " +
                        "1 as level FROM zs_knowledge_nodes " +
                        "WHERE node_type = 'feature' AND parent_id = ?";
                pstmt = conn.prepareStatement(featureSql);
                pstmt.setString(1, centerNode);
                rs = pstmt.executeQuery();

                while (rs.next()) {
                    Map<String, Object> node = new HashMap<String, Object>();
                    node.put("id", rs.getString("node_id"));
                    node.put("name", rs.getString("node_name"));
                    node.put("type", rs.getString("node_type"));
                    node.put("description", rs.getString("description"));
                    node.put("level", rs.getInt("level"));
                    nodes.add(node);
                }

            } else {
                // 默认：返回crop_1
                return getGraphNodes(conn, "crop_1");
            }

            System.out.println("查询到节点数量: " + nodes.size() + ", 中心节点: " + centerNode);
            // 统计节点类型
            Map<String, Long> typeCount = new HashMap<>();
            for (Map<String, Object> node : nodes) {
                String type = (String) node.get("type");
                typeCount.put(type, typeCount.getOrDefault(type, 0L) + 1);
            }
            System.out.println("节点类型统计: " + typeCount);

        } finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
        }

        return nodes;
    }

    private List<Map<String, Object>> getGraphLinks(Connection conn, String centerNode) throws SQLException {
        List<Map<String, Object>> links = new ArrayList<Map<String, Object>>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            String sql = "SELECT kr.source_node_id, kr.target_node_id, kr.relation_type, kr.relation_label " +
                    "FROM zs_knowledge_relations kr " +
                    "WHERE kr.source_node_id = ? OR kr.target_node_id = ? " +
                    "OR kr.source_node_id IN (SELECT target_node_id FROM zs_knowledge_relations WHERE source_node_id = ?)";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, centerNode);
            pstmt.setString(2, centerNode);
            pstmt.setString(3, centerNode);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> link = new HashMap<String, Object>();
                link.put("source", rs.getString("source_node_id"));
                link.put("target", rs.getString("target_node_id"));
                link.put("type", rs.getString("relation_type"));
                link.put("label", rs.getString("relation_label"));
                links.add(link);
            }
        } finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
        }

        return links;
    }
}